﻿using System.ComponentModel;
using System.Data;
using System.Reflection;
using ClosedXML.Excel;

namespace ConsoleApp;

/// <summary>
/// NPOI合并单元格性能较差，此处使用ClosedXML代替
/// </summary>
public static class ClosedXmlHelper
{
    /// <summary>
    /// 读取 Excel 文件为 DataTable
    /// </summary>
    /// <param name="filePath">文件路径</param>
    /// <param name="sheetName">工作表名称（可选）</param>
    /// <param name="startRow">开始行数（可选）</param>
    /// <param name="endRow">结束行数（可选）</param>
    /// <param name="hasHeaderRow">第一行是否为标题行（列名）</param>
    /// <returns>包含 Excel 数据的 DataTable</returns>
    public static DataTable Read(string filePath, string? sheetName = null,
        int? startRow = null, int? endRow = null, bool hasHeaderRow = true)
    {
        using var workbook = new XLWorkbook(filePath);
        var worksheet = string.IsNullOrEmpty(sheetName)
            ? workbook.Worksheets.First()
            : workbook.Worksheet(sheetName);

        var firstRow = startRow ??
                       (hasHeaderRow
                           ? worksheet.FirstRowUsed()!.RowNumber() + 1
                           : worksheet.FirstRowUsed()!.RowNumber());
        var lastRow = endRow ?? worksheet.LastRowUsed()!.RowNumber();

        var dataTable = new DataTable();

        // 处理标题行（如果有）
        if (hasHeaderRow)
        {
            var headerRow = worksheet.Row(worksheet.FirstRowUsed()!.RowNumber());
            foreach (var cell in headerRow.CellsUsed())
            {
                dataTable.Columns.Add(cell.GetValue<string>());
            }
        }
        else
        {
            // 如果没有标题行，使用默认列名
            var firstDataRow = worksheet.Row(firstRow);
            var columnCount = firstDataRow.CellsUsed().Count();
            for (var i = 0; i < columnCount; i++)
            {
                dataTable.Columns.Add($"Column{i + 1}");
            }
        }

        // 读取数据行
        for (var rowNum = firstRow; rowNum <= lastRow; rowNum++)
        {
            var row = worksheet.Row(rowNum);
            if (row.IsEmpty()) continue;

            var dataRow = dataTable.NewRow();
            var columnIndex = 0;

            foreach (var cell in row.CellsUsed())
            {
                // 如果数据行比标题行多，动态添加列
                if (columnIndex >= dataTable.Columns.Count)
                {
                    dataTable.Columns.Add($"Column{columnIndex + 1}");
                }

                dataRow[columnIndex] = cell.Value;
                columnIndex++;
            }

            dataTable.Rows.Add(dataRow);
        }

        return dataTable;
    }

    /// <summary>
    /// 读取
    /// </summary>
    /// <param name="filePath">文件</param>
    /// <param name="rowMapper">示例: new { Id = row.Cell(1).GetValue<int>() }</param>
    /// <param name="sheetName">名称</param>
    /// <param name="startRow">开始行数</param>
    /// <param name="endRow">结束行数</param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static List<T> ReadExcel<T>(
        string filePath,
        Func<IXLRow, T> rowMapper,
        string? sheetName = null,
        int? startRow = null,
        int? endRow = null) where T : new()
    {
        var result = new List<T>();

        using var workbook = new XLWorkbook(filePath);
        var worksheet = string.IsNullOrEmpty(sheetName)
            ? workbook.Worksheets.First()
            : workbook.Worksheet(sheetName);

        var firstRow = startRow ?? worksheet.FirstRowUsed()!.RowNumber();
        var lastRow = endRow ?? worksheet.LastRowUsed()!.RowNumber();

        for (var rowNum = firstRow; rowNum <= lastRow; rowNum++)
        {
            var row = worksheet.Row(rowNum);
            if (row.IsEmpty()) continue;

            var item = rowMapper(row);
            result.Add(item);
        }

        return result;
    }

    /// <summary>
    /// 从流中读取 Excel 数据并转换为 DataTable
    /// </summary>
    /// <param name="stream">Excel 文件流</param>
    /// <param name="sheetName">工作表名称（可选）</param>
    /// <param name="startRow">开始行数（可选）</param>
    /// <param name="endRow">结束行数（可选）</param>
    /// <param name="hasHeaderRow">第一行是否为标题行（列名）</param>
    /// <returns>包含 Excel 数据的 DataTable</returns>
    public static DataTable ReadExcel(
        this Stream stream,
        string? sheetName = null,
        int? startRow = null,
        int? endRow = null,
        bool hasHeaderRow = true)
    {
        // 确保流位置在开头
        if (stream.CanSeek && stream.Position != 0)
        {
            stream.Seek(0, SeekOrigin.Begin);
        }

        using var workbook = new XLWorkbook(stream);
        var worksheet = string.IsNullOrEmpty(sheetName)
            ? workbook.Worksheets.First()
            : workbook.Worksheet(sheetName);

        var firstRow = startRow ?? (hasHeaderRow
            ? worksheet.FirstRowUsed()!.RowNumber() + 1
            : worksheet.FirstRowUsed()!.RowNumber());
        var lastRow = endRow ?? worksheet.LastRowUsed()!.RowNumber();

        var dataTable = new DataTable();

        // 处理标题行（如果有）
        if (hasHeaderRow)
        {
            var headerRow = worksheet.Row(worksheet.FirstRowUsed()!.RowNumber());
            foreach (var cell in headerRow.CellsUsed())
            {
                dataTable.Columns.Add(cell.GetValue<string>());
            }
        }
        else
        {
            // 如果没有标题行，使用默认列名
            var firstDataRow = worksheet.Row(firstRow);
            var columnCount = firstDataRow.CellsUsed().Count();
            for (var i = 0; i < columnCount; i++)
            {
                dataTable.Columns.Add($"Column{i + 1}");
            }
        }

        // 读取数据行
        for (var rowNum = firstRow; rowNum <= lastRow; rowNum++)
        {
            var row = worksheet.Row(rowNum);
            if (row.IsEmpty()) continue;

            var dataRow = dataTable.NewRow();
            var columnIndex = 0;

            foreach (var cell in row.CellsUsed())
            {
                // 如果数据行比标题行多，动态添加列
                if (columnIndex >= dataTable.Columns.Count)
                {
                    dataTable.Columns.Add($"Column{columnIndex + 1}");
                }

                dataRow[columnIndex] = cell.Value;
                columnIndex++;
            }

            dataTable.Rows.Add(dataRow);
        }

        return dataTable;
    }

    /// <summary>
    /// 从流中读取 Excel 数据并映射到对象列表
    /// </summary>
    /// <param name="stream">Excel 文件流</param>
    /// <param name="rowMapper">行映射函数，示例: row => new { Id = row.Cell(1).GetValue<int>() }</param>
    /// <param name="sheetName">工作表名称（可选）</param>
    /// <param name="startRow">开始行数（可选）</param>
    /// <param name="endRow">结束行数（可选）</param>
    /// <typeparam name="T">返回列表中的对象类型</typeparam>
    /// <returns>映射后的对象列表</returns>
    public static List<T> ReadExcel<T>(
        this Stream stream,
        Func<IXLRow, T> rowMapper,
        string? sheetName = null,
        int? startRow = null,
        int? endRow = null) where T : new()
    {
        var result = new List<T>();

        // 确保流位置在开头（如果可搜索）
        if (stream.CanSeek && stream.Position != 0)
        {
            stream.Seek(0, SeekOrigin.Begin);
        }

        using var workbook = new XLWorkbook(stream);
        var worksheet = string.IsNullOrEmpty(sheetName)
            ? workbook.Worksheets.First()
            : workbook.Worksheet(sheetName);

        var firstRow = startRow ?? worksheet.FirstRowUsed()!.RowNumber();
        var lastRow = endRow ?? worksheet.LastRowUsed()!.RowNumber();

        for (var rowNum = firstRow; rowNum <= lastRow; rowNum++)
        {
            var row = worksheet.Row(rowNum);
            if (row.IsEmpty()) continue;

            var item = rowMapper(row);
            result.Add(item);
        }

        return result;
    }

    /// <summary>
    /// 导出DataTable到Excel
    /// </summary>
    /// <param name="dataTable">数据源</param>
    /// <param name="filePath">输出文件路径</param>
    /// <param name="mergeColumnNames">需要合并的列名集合</param>
    /// <param name="columnWidthSettings">指定列的宽度（字符数），未指定的列自动调整</param>
    /// <param name="mergeRowGroupSize">每组合并单元格的行数（必须≥2）</param>
    public static void WriteExcel(
        this DataTable dataTable,
        string filePath,
        List<string>? mergeColumnNames = null,
        Dictionary<string, int>? columnWidthSettings = null,
        int mergeRowGroupSize = 1)
    {
        // 参数校验
        if (string.IsNullOrWhiteSpace(filePath))
            throw new ArgumentException("文件路径不能为空");
        if (dataTable == null || dataTable.Rows.Count == 0)
            throw new ArgumentException("DataTable 不能为空或无数据");
        if (mergeColumnNames?.Count > 0 && mergeRowGroupSize < 2)
            throw new ArgumentException("mergeRowGroupSize 必须大于等于 2");
        if (mergeColumnNames?.Any(col => !dataTable.Columns.Contains(col)) == true)
            throw new ArgumentException("mergeColumnNames 包含不存在的列名");

        // 确保目录存在
        var dir = Path.GetDirectoryName(filePath);
        if (!string.IsNullOrEmpty(dir) && !Directory.Exists(dir))
            Directory.CreateDirectory(dir);

        using var workbook = new XLWorkbook();
        var sheet = workbook.Worksheets.Add("Sheet1");

        // 1. 写入表头
        var headerRow = sheet.Row(1);
        headerRow.Style.Font.SetBold(true);
        headerRow.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

        var columnIndexMap = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        for (var i = 0; i < dataTable.Columns.Count; i++)
        {
            var column = dataTable.Columns[i];
            sheet.Cell(1, i + 1).Value = column.ColumnName;
            columnIndexMap[column.ColumnName] = i + 1;
        }

        // 2. 批量写入数据
        for (var i = 0; i < dataTable.Rows.Count; i++)
        {
            var rowIndex = i + 2;
            var dataRow = dataTable.Rows[i];

            for (var j = 0; j < dataTable.Columns.Count; j++)
            {
                var value = dataRow[j];
                var cell = sheet.Cell(rowIndex, j + 1);

                if (value == DBNull.Value)
                {
                    cell.Value = "";
                    continue;
                }

                switch (Type.GetTypeCode(dataRow[j].GetType()))
                {
                    case TypeCode.DateTime:
                        cell.Value = (DateTime)value;
                        cell.Style.DateFormat.Format = "yyyy-MM-dd";
                        break;
                    case TypeCode.Boolean:
                        cell.Value = (bool)value;
                        break;
                    case TypeCode.Int32:
                    case TypeCode.Int64:
                    case TypeCode.Decimal:
                    case TypeCode.Double:
                    case TypeCode.Single:
                        cell.Value = Convert.ToDouble(value);
                        break;
                    default:
                        cell.Value = value.ToString();
                        break;
                }
            }
        }

        // 3. 设置列宽
        foreach (var col in dataTable.Columns.Cast<DataColumn>())
        {
            if (columnWidthSettings?.TryGetValue(col.ColumnName, out var width) == true)
            {
                width = Math.Clamp(width, 1, 255);
                sheet.Column(columnIndexMap[col.ColumnName]).Width = width;
            }
            else
            {
                var headerWidth = col.ColumnName.Sum(c => c >= 0x4E00 && c <= 0x9FFF ? 2 * 1.2 : 1.2);
                sheet.Column(columnIndexMap[col.ColumnName]).Width = Math.Clamp(headerWidth, 5, 50);
            }
        }

        // 4. 合并单元格逻辑
        if (mergeColumnNames?.Count > 0 && mergeRowGroupSize >= 2)
        {
            foreach (var colName in mergeColumnNames)
            {
                if (!columnIndexMap.TryGetValue(colName, out var colIndex))
                    continue;

                var startRow = 2;
                var totalRows = dataTable.Rows.Count + 1;

                while (startRow < totalRows)
                {
                    var cellValue = sheet.Cell(startRow, colIndex).Value;
                    var currentValue = cellValue.ToString();

                    var endRow = startRow;

                    // 允许扩展到最后一行
                    while (endRow < totalRows)
                    {
                        var nextCell = sheet.Cell(endRow + 1, colIndex).Value;
                        var nextValue = nextCell.ToString();

                        if (string.Equals(currentValue, nextValue, StringComparison.Ordinal))
                        {
                            endRow++;
                        }
                        else
                        {
                            break;
                        }
                    }

                    if (endRow > startRow)
                    {
                        var actualEndRow = Math.Min(endRow, startRow + mergeRowGroupSize - 1);
                        if (actualEndRow > startRow)
                        {
                            // 设置合并后的单元格居中
                            var range = sheet.Range(startRow, colIndex, actualEndRow, colIndex);
                            range.Merge();
                            range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                            range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                            startRow = actualEndRow + 1;
                            continue;
                        }
                    }

                    startRow++;
                }
            }
        }

        workbook.SaveAs(filePath);
    }

    /// <summary>
    /// 导出 List&lt;T&gt; 到 Excel
    /// </summary>
    /// <typeparam name="T">数据类型</typeparam>
    /// <param name="data">数据源</param>
    /// <param name="filePath">输出文件路径</param>
    /// <param name="mergePropertyNames">需要合并的属性名集合（对应 T 的属性）</param>
    /// <param name="columnWidthSettings">指定列的宽度（字符数），未指定的列自动调整</param>
    /// <param name="mergeRowGroupSize">每组合并单元格的行数（必须≥2）</param>
    /// <param name="headerDisplayNameMapping">自定义表头显示名称（属性名 -> 显示名称）</param>
    public static void WriteAs<T>(
        this List<T> data,
        string filePath,
        List<string>? mergePropertyNames = null,
        Dictionary<string, int>? columnWidthSettings = null,
        int mergeRowGroupSize = 1,
        Dictionary<string, string>? headerDisplayNameMapping = null)
    {
        // 参数校验
        if (string.IsNullOrWhiteSpace(filePath))
            throw new ArgumentException("文件路径不能为空");
        if (data == null || data.Count == 0)
            throw new ArgumentException("数据源不能为空");
        if (mergePropertyNames?.Count > 0 && mergeRowGroupSize < 2)
            throw new ArgumentException("mergeRowGroupSize 必须大于等于 2");

        // 确保目录存在
        var dir = Path.GetDirectoryName(filePath);
        if (!string.IsNullOrEmpty(dir) && !Directory.Exists(dir))
            Directory.CreateDirectory(dir);

        using var workbook = new XLWorkbook();
        var sheet = workbook.Worksheets.Add("Sheet1");

        // 获取 T 的所有公共属性
        var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        if (properties.Length == 0)
            throw new InvalidOperationException("类型 T 必须包含至少一个公共属性");

        // 1. 写入表头（支持自定义显示名称）
        var headerRow = sheet.Row(1);
        headerRow.Style.Font.SetBold(true);
        headerRow.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

        var columnIndexMap = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        for (var i = 0; i < properties.Length; i++)
        {
            var prop = properties[i];
            var columnName = prop.Name;

            // 使用自定义表头名称（如果存在）
            var displayName = headerDisplayNameMapping?.TryGetValue(columnName, out var customName) == true
                ? customName
                : GetPropertyDisplayName(prop);

            sheet.Cell(1, i + 1).Value = displayName;
            columnIndexMap[columnName] = i + 1;
        }

        // 2. 批量写入数据
        for (var i = 0; i < data.Count; i++)
        {
            var rowIndex = i + 2;
            var item = data[i];

            for (var j = 0; j < properties.Length; j++)
            {
                var prop = properties[j];
                var value = prop.GetValue(item);
                var cell = sheet.Cell(rowIndex, j + 1);

                if (value == null)
                {
                    cell.Value = "";
                    continue;
                }

                switch (Type.GetTypeCode(value.GetType()))
                {
                    case TypeCode.DateTime:
                        cell.Value = (DateTime)value;
                        cell.Style.DateFormat.Format = "yyyy-MM-dd";
                        break;
                    case TypeCode.Boolean:
                        cell.Value = (bool)value;
                        break;
                    case TypeCode.Int32:
                    case TypeCode.Int64:
                    case TypeCode.Decimal:
                    case TypeCode.Double:
                    case TypeCode.Single:
                        cell.Value = Convert.ToDouble(value);
                        break;
                    default:
                        cell.Value = value.ToString();
                        break;
                }
            }
        }

        // 3. 设置列宽
        foreach (var prop in properties)
        {
            var columnName = prop.Name;
            if (!columnIndexMap.TryGetValue(columnName, out var colIndex)) continue;
            if (columnWidthSettings?.TryGetValue(columnName, out var width) == true)
            {
                width = Math.Clamp(width, 1, 255);
                sheet.Column(colIndex).Width = width;
            }
            else
            {
                // 自动计算列宽（支持中文）
                var displayName = headerDisplayNameMapping?.TryGetValue(columnName, out var customName) == true
                    ? customName
                    : GetPropertyDisplayName(prop);
                var headerWidth = displayName.Sum(c => c >= 0x4E00 && c <= 0x9FFF ? 2 * 1.2 : 1.2);
                sheet.Column(colIndex).Width = Math.Clamp(headerWidth, 5, 50);
            }
        }

        // 4. 合并单元格逻辑
        if (mergePropertyNames?.Count > 0 && mergeRowGroupSize >= 2)
        {
            foreach (var propName in mergePropertyNames)
            {
                if (!columnIndexMap.TryGetValue(propName, out var colIndex))
                    continue;

                var startRow = 2;
                var totalRows = data.Count + 1;

                while (startRow < totalRows)
                {
                    var cellValue = sheet.Cell(startRow, colIndex).Value;
                    var currentValue = cellValue.ToString();

                    var endRow = startRow;

                    while (endRow < totalRows)
                    {
                        var nextCell = sheet.Cell(endRow + 1, colIndex).Value;
                        var nextValue = nextCell.ToString();

                        if (string.Equals(currentValue, nextValue, StringComparison.Ordinal))
                        {
                            endRow++;
                        }
                        else
                        {
                            break;
                        }
                    }

                    if (endRow > startRow)
                    {
                        var actualEndRow = Math.Min(endRow, startRow + mergeRowGroupSize - 1);
                        if (actualEndRow > startRow)
                        {
                            var range = sheet.Range(startRow, colIndex, actualEndRow, colIndex);
                            range.Merge();
                            range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                            range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                            startRow = actualEndRow + 1;
                            continue;
                        }
                    }

                    startRow++;
                }
            }
        }

        workbook.SaveAs(filePath);
    }

    // 获取属性的显示名称（支持 DisplayNameAttribute）
    private static string GetPropertyDisplayName(PropertyInfo prop)
    {
        var attr = prop.GetCustomAttribute<DisplayNameAttribute>();
        return attr?.DisplayName ?? prop.Name;
    }
}